package rubix.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class SupplierManager {

    private static final String TBLNAME = "supplier";

    private static void handleSQLException(SQLException ex, String sql, String... parameters) {
        String msg = "Unable to access data; SQL=" + sql + "\n";

        for (String parameter : parameters) {
            msg += "," + parameter;
        }

        Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, msg, ex);
        throw new RuntimeException(msg, ex);
    }
    //method
    public static void prepareTables(){
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";
        
        try {
            conn = ConnectionManager.getConnection();            
            
            sql = "DROP TABLE IF EXISTS supplier";
            stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
            
            sql = "CREATE TABLE IF NOT EXISTS supplier("
                    + "supplierid VARCHAR(20) NOT NULL,"
                    + "supplierdescription VARCHAR(100) NOT NULL,"
                    + "supplierlocation VARCHAR(7) NOT NULL,"                  
                    + "PRIMARY KEY (supplierid)"
                    + ")";
            stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "prepareTables");
        } finally {
            ConnectionManager.close(conn, stmt);
        }
    }    
    
    public static void addSupplier(Supplier supplier) {
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";
        
        String supplierId = supplier.getSupplierId();
        String supplierDescription = supplier.getSupplierDescription();
        String supplierLocation = supplier.getSupplierLocation();
        
        try {
            conn = ConnectionManager.getConnection();
            
            sql = "INSERT INTO " + TBLNAME + " (supplierid,supplierdescription,supplierlocation) VALUES (?,?,?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, supplierId);
            stmt.setString(2, supplierDescription);
            stmt.setString(3, supplierLocation);

            stmt.executeUpdate();
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "supplierid=" + supplierId + ",supplierdescription=" + supplierDescription + ",supplierlocation=" + supplierLocation);
        } finally {
            ConnectionManager.close(conn, stmt);
        }
    }
    
    public static ArrayList<Supplier> retrieveAll() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "";
        ArrayList<Supplier> results = new ArrayList<Supplier>();

        try {
            conn = ConnectionManager.getConnection();

            sql = "SELECT supplierid,supplierdescription,supplierlocation FROM " + TBLNAME;
            stmt = conn.prepareStatement(sql);

            rs = stmt.executeQuery();
            while (rs.next()) {
                String supplierId = rs.getString("supplierid");
                String supplierDescription = rs.getString("supplierdescription");
                String supplierLocation = rs.getString("supplierlocation");

                results.add(new Supplier(supplierId,supplierDescription,supplierLocation));
            }
        } catch (SQLException ex) {
            handleSQLException(ex, sql);
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        return results;
    }

    public static Supplier retrieveSupplier(String supplierId) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "";
        Supplier result = null;

        try {
            conn = ConnectionManager.getConnection();

            sql = "SELECT supplierdescription,supplierlocation FROM " + TBLNAME + " WHERE supplierid = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, supplierId);

            rs = stmt.executeQuery();

            while (rs.next()) {
                String supplierDescription = rs.getString("supplierdescription");
                String supplierLocation = rs.getString("supplierlocation");
                
                result = new Supplier(supplierId,supplierDescription,supplierLocation);
            }
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "supplierid=" + supplierId);
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        return result;
    }
    
    public static void updateSupplierLocatoin(String supplierId, String location) {
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";

        try {
            conn = ConnectionManager.getConnection();

            sql = "UPDATE " + TBLNAME + " set supplierlocation=? where supplierid = ?";
            stmt = conn.prepareStatement(sql);

            stmt.setString(1, location);
            stmt.setString(2, supplierId);

            stmt.executeUpdate();
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "supplierid=" + supplierId + ",supplierlocation=" + location);
        } finally {
            ConnectionManager.close(conn, stmt);
        }
    }
}